package cn.edu.hbwe.book.dao.impl;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import cn.edu.hbwe.book.dao.NoticeDao;
import cn.edu.hbwe.book.dao.OrderDao;
import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.entity.Order;
import cn.edu.hbwe.book.entity.User;
import cn.edu.hbwe.book.utils.C3p0Util;
import cn.edu.hbwe.book.utils.DBCPUtil;
import cn.edu.hbwe.book.utils.DuridUtil;


public class OrderDaoImpl implements OrderDao{

		public void addProduct(Order order) throws SQLException {
			// 1.生成Sql语句
			String sql = "insert into orders values(?,?,?,?,?,0,null,?)";
			// 2.生成执行sql语句的QueryRunner,不传递参数
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
	        // 3.执行update()方法插入数据
			runner.update(conn, sql, order.getId(),
					order.getMoney(), order.getReceiverAddress(), order
							.getReceiverName(), order.getReceiverPhone(), order
							.getUser().getId());
		}
		/**
		 *  查找所有订单
		 * @return
		 * @throws SQLException 
		 */
		public List<Order> findAllOrder() throws SQLException {
			//1.创建sql
			String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id order by orders.user_id";
			//2.创建QueryRunner对象
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
	        //3.返回QueryRunner对象query()方法的查询结果
			return runner.query(conn,sql, new ResultSetHandler<List<Order>>() {			
				public List<Order> handle(ResultSet rs) throws SQLException {
					//创建订单集合
					List<Order> orders = new ArrayList<Order>();
	                //循环遍历订单和用户信息
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));
						orders.add(order);

						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);
					}
					return orders;
				}
			});
		}	
		public List<Order> findOrderByManyCondition(String id, String receiverName) throws SQLException {
			//1.创建集合对象
			List<Object> objs = new ArrayList<Object>();
			//2.定义查询sql
			String sql = "select orders.*,user.* from orders,user where user.id=orders.user_id ";
			//3.根据参数拼接sql语句
			if (id != null && id.trim().length() > 0) {
				sql += " and orders.id=?";
				objs.add(id);
			}
			if (receiverName != null && receiverName.trim().length() > 0) {
				sql += " and receiverName=?";
				objs.add(receiverName);
			}
			sql += " order by orders.user_id";
			//4.创建QueryRunner对象
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			//5.返回QueryRunner对象query方法的执行结果
			return runner.query(conn,sql, new ResultSetHandler<List<Order>>() {
				public List<Order> handle(ResultSet rs) throws SQLException {
					List<Order> orders = new ArrayList<Order>();
	               //循环遍历出订单和用户信息
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs
								.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));
						orders.add(order);
						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);

					}

					return orders;
				}
			}, objs.toArray());
		}
		

		//根据登陆的用户查找用户的订单
		public List<Order> findOrderByUser(User user) throws Exception {
			//1.生成sql语句
			String sql = "select * from orders where user_id=?";
			//2.创建QueryRunner对象
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			//3.返回QueryRunner对象query()方法的查询结果
			return runner.query(conn,sql, new ResultSetHandler<List<Order>>() {
				public List<Order> handle(ResultSet rs) throws SQLException {
					//创建订单集合
					List<Order> orders = new ArrayList<Order>();
					//循环遍历订单
					while (rs.next()) {
						Order order = new Order();
						order.setId(rs.getString("id"));
						order.setMoney(rs.getDouble("money"));
						order.setOrdertime(rs.getDate("ordertime"));
						order.setPaystate(rs.getInt("paystate"));
						order.setReceiverAddress(rs.getString("receiverAddress"));
						order.setReceiverName(rs.getString("receiverName"));
						order.setReceiverPhone(rs.getString("receiverPhone"));
						order.setUser(user);
						orders.add(order);
					}
					return orders;
				}
			}, user.getId());
		}
		//根据订单的id查找订单的详细信息
		public Order findOrderById(String id) throws SQLException {
			//1.生成sql语句
			String sql = "select * from orders,user where orders.user_id=user.id and orders.id=?";
			//2.创建QueryRunner对象
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			//3.返回QueryRunner对象query()方法的查询结果
			return runner.query(conn,sql, new ResultSetHandler<Order>() {
				public Order handle(ResultSet rs) throws SQLException {
					//创建订单
					Order order = new Order();
					//循环遍历订单
					while (rs.next()) {
						order.setId(rs.getString("orders.id"));
						order.setMoney(rs.getDouble("orders.money"));
						order.setOrdertime(rs.getDate("orders.ordertime"));
						order.setPaystate(rs.getInt("orders.paystate"));
						order.setReceiverAddress(rs.getString("orders.receiverAddress"));
						order.setReceiverName(rs.getString("orders.receiverName"));
						order.setReceiverPhone(rs.getString("orders.receiverPhone"));
						//循环遍历用户信息
						User user = new User();
						user.setId(rs.getInt("user.id"));
						user.setEmail(rs.getString("user.email"));
						user.setGender(rs.getString("user.gender"));
						user.setActiveCode(rs.getString("user.activecode"));
						user.setIntroduce(rs.getString("user.introduce"));
						user.setPassword(rs.getString("user.password"));
						user.setRegistTime(rs.getDate("user.registtime"));
						user.setRole(rs.getString("user.role"));
						user.setState(rs.getInt("user.state"));
						user.setTelephone(rs.getString("user.telephone"));
						user.setUsername(rs.getString("user.username"));
						order.setUser(user);
					}
					return order;
				}
			}, id);
		}
		//根据id删除订单
		public void delOrderById(String id) throws SQLException {
			String sql="delete from orders where id=?";		
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			runner.update(conn,sql,id);		
		}

		/**
		 *  根据订单号修改订单状态
		 * @param id
		 * @throws SQLException
		 */
		public void updateOrderState(String id) throws SQLException {
			String sql = "update orders set paystate=1 where id=?";
			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			runner.update(conn,sql, id);
		}
	
}
